Livrable 1
Equipe 10 :
- AUROY Ethan
- DAS-NEVES Cendro
- HEBERT HERVAGAULT Clément
- LINARD Raphaël
- THEAUDIERE Solal
Sommaire :
1. Introduction
2. Le dictionnaire des données
3. MCD
4. MLD
5. MPD
6. Requêtes
1. Introduction
Pour rappel, le projet vise à concevoir une base de données centralisée pour stocker et analyser les données sur la qualité de l’air en France.
Phase 1 :
Nous devons livrer le dictionnaire des données, le MCD, le MLD, le script SQL et les arbres algébriques des différentes requêtes demandées.
Expliquer ce qu’est :
-
Le dictionnaire de données est l’ensemble des données de référence nécessaire à la création d’une base de donnée relationnelle.
-
Le modèle conceptuel de données est une représentation de manière abstraite et formelle de la structure d’une BDD.
-
Le modèle logique de données est simplement la traduction en script SQL de la modélisation. Il s’agit de la représentation en ligne du schéma représentant la structure de la base de données.
-
Le modèle physique de données est le script SQL final comprenant notamment les types et tailles des données.
-
Les arbres algébriques sont la représentation d’une requête SQL sous la forme d’un arbre. Les feuilles de l’arbre représentent les tables de départ.
Relations entre MCD, MLD et MPD
- MCD : C’est le point de départ, une vue conceptuelle et abstraite des données.
- MLD : Une étape intermédiaire qui traduit le MCD en structures logiques adaptées à l’implémentation.
- MPD : La dernière étape, qui concrétise le modèle logique en une base de données physique optimisée pour un SGBD particulier.
2. Le dictionnaire des données
Un dictionnaire de données joue un rôle essentiel dans la création d’une base de données en fournissant une description structurée et normalisée des éléments qui la composent. Il définit les informations sur les tables, les champs, les types de données et les relations. Ce référentiel centralisé facilite la cohérence, la maintenance, la documentation et la gouvernance des données, tout en assurant une communication claire entre tous les membres du projet et du client.
Le dictionnaire de données joue également un rôle important dans l’élaboration de la matrice des dépendances et du Modèle Conceptuel de Données (MCD).
| Nom |
Code |
Type |
Taille |
Min |
Max |
Commentaire |
| Identifiant du gaz |
id_gaz |
INTEGER (N) |
10 |
1 |
/ |
Clé primaire |
| Nom du gaz |
gaz_nom |
VARCHAR (C) |
100 |
|
/ |
|
| Sigle du gaz |
gaz_sigle |
VARCHAR (C) |
10 |
|
/ |
|
| Type du gaz |
gaz_type |
VARCHAR (C) |
5 |
|
/ |
|
| Identifiant du capteur |
id_capteur |
INTEGER (N) |
10 |
1 |
/ |
Clé primaire |
| État du capteur |
capt_etat |
BOOLEAN |
1 |
0 |
1 |
NOT NULL |
| Identifiant de la ville |
id_ville |
INTEGER (N) |
10 |
1 |
/ |
Clé primaire |
| Nom de la ville |
ville_nom |
VARCHAR (C) |
50 |
|
/ |
|
| Code postal de la ville |
ville_cp |
VARCHAR (C) |
5 |
|
/ |
|
| Identifiant de l’adresse |
id_adresse |
INTEGER (N) |
10 |
1 |
/ |
Clé primaire |
| Numéro de l’adresse |
adr_numero |
VARCHAR (C) |
10 |
|
/ |
|
| Rue de l’adresse |
adr_rue |
VARCHAR (C) |
150 |
|
/ |
|
| Complément de l’adresse |
adr_complement |
VARCHAR (C) |
150 |
|
/ |
|
| Identifiant de la date |
id_date |
INTEGER (N) |
10 |
1 |
/ |
Clé primaire |
| Date |
d_date |
DATE |
/ |
01/01/1900 |
/ |
|
| Identifiant personnel |
id_personnel |
INTEGER (N) |
10 |
1 |
/ |
Clé primaire |
| Nom du personnel |
pers_nom |
VARCHAR (C) |
50 |
|
/ |
|
| Prénom du personnel |
pers_prenom |
VARCHAR (C) |
50 |
|
/ |
|
| Dernier diplôme obtenu |
pers_diplome |
VARCHAR (C) |
50 |
|
/ |
|
| Identifiant de l’agence |
id_agence |
INTEGER (N) |
10 |
1 |
/ |
Clé primaire |
| Région de l’agence |
ag_region |
VARCHAR (C) |
50 |
|
/ |
|
| Identifiant du relevé |
id_releve |
VARCHAR (C) |
20 |
|
/ |
Clé primaire |
| Donnée relevée |
rel_donnee |
DECIMAL(5,2) |
50 |
0,01 |
500 |
Mesuré en ppm |
| Identifiant rapport |
id_rapport |
VARCHAR (C) |
20 |
|
/ |
Clé primaire |
| Titre du rapport |
rap_titre |
VARCHAR (C) |
150 |
|
/ |
|
| Analyse du rapport |
rap_analyse |
TEXT (C) |
20k |
|
/ |
|
| Identifiant poste |
id_poste |
INTEGER (N) |
10 |
1 |
/ |
Clé primaire |
| Nom du poste |
post_nom |
VARCHAR (C) |
50 |
|
/ |
|
Matrice des dépendances
|
id_gaz |
id_releve |
id_capteur |
id_agence |
id_adresse |
id_personnel |
id_rapport |
id_date |
id_ville |
id_poste |
| id_gaz |
/ |
|
|
|
|
|
|
|
|
|
| gaz_nom |
1 |
|
|
|
|
|
|
|
|
|
| gaz_sigle |
1 |
|
|
|
|
|
|
|
|
|
| gaz_type |
1 |
|
|
|
|
|
|
|
|
|
| id_capteur |
|
|
/ |
|
|
|
|
|
|
|
| capt_etat |
|
|
1 |
|
|
|
|
|
|
|
| id_ville |
|
|
|
|
|
|
|
|
/ |
|
| ville_nom |
|
|
|
|
|
|
|
|
1 |
|
| ville_cp |
|
|
|
|
|
|
|
|
1 |
|
| id_adresse |
|
|
|
|
/ |
|
|
|
|
|
| adr_numero |
|
|
|
|
1 |
|
|
|
|
|
| adr_rue |
|
|
|
|
1 |
|
|
|
|
|
| adr_complement |
|
|
|
|
1 |
|
|
|
|
|
| id_date |
|
|
|
|
|
|
|
/ |
|
|
| d_date |
|
|
|
|
|
|
|
1 |
|
|
| id_personnel |
|
|
|
|
|
/ |
|
|
|
|
| pers_nom |
|
|
|
|
|
1 |
|
|
|
|
| pers_prenom |
|
|
|
|
|
1 |
|
|
|
|
| pers_diplome |
|
|
|
|
|
1 |
|
|
|
|
| id_agence |
|
|
|
/ |
|
|
|
|
|
|
| ag_region |
|
|
|
1 |
|
|
|
|
|
|
| id_releve |
|
/ |
|
|
|
|
|
|
|
|
| rel_donnee |
|
1 |
|
|
|
|
|
|
|
|
| id_rapport |
|
|
|
|
|
|
/ |
|
|
|
| rap_titre |
|
|
|
|
|
|
1 |
|
|
|
| rap_analyse |
|
|
|
|
|
|
1 |
|
|
|
| id_poste |
|
|
|
|
|
|
|
|
|
/ |
| post_nom |
|
|
|
|
|
|
|
|
|
1 |
3. MCD
-
Définition : Le MCD est une représentation abstraite et indépendante des données d’un système d’information. Il permet de décrire les entités, les relations entre ces entités, et les règles qui les régissent.
-
Objectif : L’objectif principal du MCD est de fournir une vue globale et compréhensible des données manipulées par le système, sans se préoccuper des aspects techniques de stockage ou de gestion.
-
Éléments :
- Entités : Représentent des objets ou concepts du monde réel (ex. : Client, Produit).
- Attributs : Caractéristiques ou propriétés des entités (ex. : Nom, Adresse).
- Relations : Associations entre les entités (ex. : Un client passe une commande).
- Cardinalités : Indiquent le nombre d’occurrences d’une entité associée à une autre (ex. : Un client peut passer plusieurs commandes).
Nous avons schématiser le Modèle Conceptuel de Données (MCD) grâce à l’application Looping, qui permet de représenter graphiquement celui-ci, ainsi que faciliter les étapes de conception suivantes.

Notre MCD est composé de 10 tables et 19 associations.
Description des Tables
-
Personnel
- Attributs : Identifiant du personnel, Nom du personnel, Prénom du personnel, Dernier diplôme obtenu.
- Description : Cette table contient les informations de base sur les employés ou le personnel de l’agence.
-
Adresse
- Attributs : Identifiant de l’adresse, Numéro de l’adresse, Rue de l’adresse, Complément de l’adresse.
- Description : Cette table stocke les informations d’adresse, qui peuvent être liées à différentes entités comme le personnel ou les agences.
-
Agence
- Attributs : Identifiant de l’agence, Région de l’agence.
- Description : Cette table représente les différentes agences, chacune identifiée par un identifiant unique et associée à une région spécifique.
-
Capteur
- Attributs : Identifiant du capteur, État du capteur.
- Description : Cette table contient les informations liés aux capteurs.
-
Gaz
- Attributs : Identifiant du gaz, Nom du gaz, Symbole du gaz, Type du gaz.
- Description : Cette table stocke les informations sur les différents types de gaz surveillés.
-
Rapport
- Attributs : Identifiant du rapport, Titre du rapport, Analyse du rapport.
- Description : Cette table contient les rapports rédigés, chacun ayant une analyse associée.
-
Relève
- Attributs : Identifiant du relevé, Donnée relevée.
- Description : Cette table stocke les relevés de données effectués par les capteurs.
-
Dates
- Attributs : Identifiant de la date, Date.
- Description : Cette table contient les informations de date, utilisées pour enregistrer les événements temporels.
-
Poste
- Attributs : Identifiant du poste, Nom du poste.
- Description : Cette table représente les différents postes.
-
Ville
- Attributs : Identifiant de la ville, Nom de la ville, Code postal de la ville.
- Description : Cette table représente les informations à propos des différentes villes.
Associations et Cardinalités
-
Habiter
- Entités : Personnel, Adresse.
- Cardinalité : 1,1 (Personnel) à 0,n (Adresse).
- Description : Un personnel peut habiter à une adresse, mais un personnel ne peut habiter à plusieurs adresses.
Une adresse peut être associée à une seul personne et une adresse peut être associée à plusieurs personnes.
-
Gérer
- Entités : Personnel, Capteur.
- Cardinalité : 0,n (Personnel) à 0,1 (Capteur).
- Description : Un personnel peut ne pas gérer de capteurs et un personnel peut gérer plusieurs capteurs.
Un capteur est géré par un seul personnel, mais un capteur ne peut pas être géré par plusieurs personnes.
-
Travailler
- Entités : Personnel, Agence.
- Cardinalité : 1,1 (Personnel) à 0,n (Agence).
- Description : Un personnel est obligé de travailler dans au moins une agence et un personnel peut travailler dans une seule agence.
Une agence n’est pas obligé d’avoir du personnel mais une agence peut avoir plusieurs personnels.
-
Rédiger
- Entités : Personnel, Rapport.
- Cardinalité : 0,n (Personnel) à 1,n (Rapport).
- Description : Un personnel peut avoir rédigé aucun rapport et un personnel peut rédiger plusieurs rapports.
Un rapport est obligatoirement rédigé par un personnel mais un rapport peut être rédigé par plusieurs personnels.
-
Naître
- Entités : Personnel, Date.
- Cardinalité : 1,1 (Personnel) à 0,n (Date).
- Description : Un personnel est né à une seule date spécifique.
Une date ne correspond pas obligatoirement à une date de naissance d’un membre du personnel mais plusieurs personnes peuvent être nées le même jour.
-
Débuter
- Entités : Personnel, Date.
- Cardinalité : 1,1 (Personnel) à 0,n (Date).
- Description : Un personnel débute son travail à une seule date spécifique. Une date ne correspond pas obligatoirement à la date de début d’un membre du personnel mais plusieurs personnes peuvent débuter le même jour.
-
Dater
- Entités : Rapport, Date.
- Cardinalité : 1,1 (Rapport) à 0,n (Date).
- Description : Un rapport est daté à une seule date spécifique.
Une date ne correspond pas obligatoirement à la date de rédaction d’un rapport mais plusieurs rapports peuvent être datés le même jour.
-
Se situer
- Entités : Capteur, Adresse.
- Cardinalité : 1,1 (Capteur) à 0,n (Adresse).
- Description : Un capteur est situé à une seule adresse spécifique.
Une adresse ne situe pas obligatoirement un capteur mais une adresse peut avoir plusieurs capteurs.
-
Installer
- Entités : Capteur, Agence.
- Cardinalité : 1,1 (Capteur) à 0,n (Agence).
- Description : Un capteur est obligatoirement attaché à une agence et un capteur est attaché à qu’une seule agence.
Une agence n’est pas obligé d’avoir de capteurs mais une agence peut avoir plusieurs capteurs.
-
Effectuer
- Entités : Capteur, Relevé
- Cardinalité : 0,n (Capteur) à 1,1 (Relevé).
- Description : Un capteur n’est pas obligé de faire un relevé mais un capteur peut effectuer plusieurs relevés.
Un relevé est effectué par un seul capteur.
-
Identifier
- Entités : Gaz, Relevé.
- Cardinalité : 0,1 (Gaz) à 1,1 (Relevé).
- Description : Un gaz n’est pas obligé d’être identifié par un relevé mais un gaz peut être identifié par qu’un seul relevé. Un relevé peut identifier qu’un seul gaz.
-
Rapporter
- Entités : Rapport, Relevé.
- Cardinalité : 1,n (Rapport) à 0,n (Relevé).
- Description : Un rapport est obligatoirement rapporté par un relevé mais un rapport peut être rapporté par plusieurs relevés.
Un relevé n’est pas obligé de rapporter un rapport mais un relevé peut rapporter plusieurs rapports.
-
Localiser
- Entités : Adresse, Agence
- Cardinalité : 0,1 (Adresse) à 1,1 (Agence).
- Description : Une adresse ne correspond pas obligatoirement à une agence et une adresse peut correspondre une seule agence.
Une agence se localise à une seule adresse.
-
Faire
- Entités : Agence, Relevé.
- Cardinalité : 0,n (Agence) à 1,1 (Relevé).
- Description : Une agence ne fait pas obligatoirement un relevé mais une agence peut faire plusieurs relevés.
Un relevé est fait par une seule agence.
-
Capter
- Entités : Capteur, Gaz.
- Cardinalité : 1,1 (Capteur) à 0,n (Gaz).
- Description : Un capteur capte qu’un seul gaz.
Un gaz n’est pas obligatoirement capté par un capteur mais un gaz peut être capté par plusieurs capteurs.
-
Dater
- Entités : Relevé, Date.
- Cardinalité : 1,1 (Relevé) à 0,n (Date).
- Description : Un relevé est daté à une seule date spécifique.
Une date ne correspond pas obligatoirement à la date d’un relevé mais plusieurs relevés peuvent être datés le même jour.
- Rédiger
- Entités : Agence, Rapport.
- Cardinalité : 0,n (Agence) à 1,1 (Rapport).
- Description : Une agence peut avoir rédigé aucun rapport et un agence peut rédiger plusieurs rapports.
Un rapport est toujours rédigé par une seule agence.
- Occuper
- Entités : Poste, Personnel.
- Cardinalité : 0,n (Poste) à 1,1 (Personnel).
- Description : Un poste n’est pas obligé d’être attaché à une personne mais un poste peut être attaché à plusieurs personnes. Un personnel a un unique poste.
- Se localiser
- Entités : Ville, Adresse.
- Cardinalité : 0,n (Ville) à 1,1 (Adresse).
- Description : Une ville n’est pas obligé de localiser une adresse mais une ville peut localiser plusieurs adresses.
Une adresse se localise dans une seule ville.
4. MLD
-
Définition : Le MLD est une transformation du MCD en un modèle plus proche de la réalisation technique. Il traduit les concepts du MCD en structures de données qui peuvent être implémentées dans une base de données.
-
Objectif : Le MLD vise à organiser les données de manière à ce qu’elles puissent être stockées et manipulées efficacement dans un système de gestion de base de données (SGBD).
-
Éléments :
- Tables : Correspondent aux entités du MCD.
- Colonnes : Représentent les attributs des entités.
- Clés primaires et étrangères : Assurent l’intégrité des données et les relations entre les tables.
- Types de données : Spécifient le format des données stockées dans les colonnes.
À partir du MCD, nous pouvons transformer celui-ci en MLD sur Looping, et celui-ci nous renvoie le MLD suivant sans erreur, nous pouvons donc conclure que celui-ci est correct.

À noter que plusieurs clés étrangères ne sont pas correctement nommées, comme id_date et id_date_1 dans la table Personnel. En effet, la conversion du MCD au MLD est automatique, donc le logiciel les a nommé par défaut. Nous ne pouvons pas les modifier donc nous les modifierons dans le script SQL.
5. MPD
-
Définition : Le MPD est la représentation concrète et technique des données, adaptée à un SGBD spécifique. Il décrit comment les données seront physiquement stockées et organisées dans la base de données.
-
Objectif : Le MPD a pour but d’optimiser les performances et l’efficacité du stockage et de l’accès aux données, en tenant compte des spécificités du SGBD utilisé.
-
Éléments :
- Schémas de tables : Définissent la structure des tables avec des détails techniques.
- Index : Améliorent les performances des requêtes.
- Partitions : Divisent les grandes tables pour améliorer la gestion des données.
- Contraintes : Assurent l’intégrité des données au niveau physique.
Nous avons joint le Modèle Physique de Données (MPD) en document texte, nous avons construit celui-ci encore une fois grâce à l’aide de Looping.
6. Arbres algébriques
1. Liste de l’ensemble des agences

2. Liste de l’ensemble du personnel technique de l’agence de Bordeaux

3. Nombre total de capteurs déployés.

4. Liste des rapports publiés entre 2018 et 2022.

5. Afficher les concentrations de CH4 (en ppm) dans les régions « Ile-de-France », « Bretagne » et « Occitanie » en mai et juin 2023.

6. Liste des noms des agents techniques maintenant des capteurs concernant les gaz à effet de serre provenant de l’industrie (GESI).

7. Titres et dates des rapports concernant des concentrations de NH3
